#include "yk_form_kccx.h"
#include "ui_yk_form_kccx.h"
#include <QSettings>
#include <QMessageBox>
#include <QJsonDocument>
#include <QHeaderView>
#include <QSqlError>
#include <QSqlRecord>
#include "pub_var.h"
#include <QJsonObject>
#include <QJsonArray>
#include <windows.h>
#include <QLibrary>
#include <QAxObject>

Form_kccx::Form_kccx(QWidget *parent) :
    QWidget(parent),
    ui(new Ui::Form_kccx)
{
    ui->setupUi(this);

    //恢复当前窗口位置
    QSettings setIni("clin.ini", QSettings::IniFormat);
    QRect rct = setIni.value("windowState/kccx").toRect();
    this->setGeometry(rct);

    //数据库连接
    qrm = new QSqlQuery(pub::db_m);
    qro = new QSqlQuery(pub::db_o);
    mdList1 = new QSqlQueryModel();
    mdList2 = new QSqlQueryModel();
    mdList3 = new QSqlQueryModel();
    mdList4 = new QSqlQueryModel();
    mdSearchPop = new QSqlQueryModel();
    sq  = "select dept_name, dept_code, dept_type from com_department "
          " where dept_type in ('P','PI') order by dept_type,dept_name";
    store_depts = pub::fillCombo(ui->cbbStoreDept, sq, -1);
    ui->cbbStoreDept->addItem("全部");
    ui->cbbStoreDept->setCurrentIndex( ui->cbbStoreDept->count() - 1 );

    dlgDelay = new Pub_pop_delay();
    dlgGetItem = new Pub_pop_get_item();

    //ui->deBegin_3->setDate(QDate::currentDate());
    //ui->deEnd_3->setDate(QDate::currentDate());
    ui->deBegin->setDate(QDate::currentDate());
    ui->deEnd->setDate(QDate::currentDate());

    ui->tabWidget->setCurrentIndex(0);  //库存查询页面
    //    sqlThread = new sqThread();
    //    connect( sqlThread, &sqThread::message, this, &Form_ys_bzgl::threadMsg);

    ui->cbbRelate->addItem("全部");   //默认往来关系，全部

    ui->ckbRelattion->setVisible(false);//按往来关系汇总
    ui->ckbIOType->setVisible(false);//按出入方式汇总
    //药品成本临时表
    sq = "create temporary table if not exists tmp_drug_cost (";
    sq += "atinc int auto_increment not null key, ";  //流水编码
    sq += "日期 VARCHAR(12) not null , ";  //
    sq += "西药 VARCHAR(12) not null,";          //
    sq += "中成药 VARCHAR(12) not null, ";      //
    sq += "中草药 VARCHAR(12) not null, ";      //
    sq += "小计 VARCHAR(12) not null";               //
    sq += ") ";   //
    if(!qrm->exec(sq))
    {
        QMessageBox::information(this,"note",qrm->lastError().text());
        return;
    }

    //自制药品临时表
    sq = "create temporary table if not exists tmp_self_drug (";
    sq += "atinc int auto_increment not null key, ";  //流水编码
    sq += "用药诊室 VARCHAR(12) not null , ";  //
    sq += "药品类别 VARCHAR(12) not null,";          //
    sq += "药品名称 VARCHAR(12) not null, ";      //
    sq += "规格 VARCHAR(12) not null, ";      //
    sq += "单位 VARCHAR(12) not null , ";  //
    sq += "数量 VARCHAR(12) not null,";          //
    sq += "进价 VARCHAR(12) not null, ";      //
    sq += "零售价 VARCHAR(12) not null,";               //
    sq += "进价总额 VARCHAR(12) not null, ";      //
    sq += "零售总额 VARCHAR(12) not null,";               //
    sq += "差价 VARCHAR(12) not null";               //
    sq += ") ";   //
    if(!qrm->exec(sq))
    {
        QMessageBox::information(this,"note",qrm->lastError().text());
        return;
    }

}

Form_kccx::~Form_kccx()
{
    delete ui;
}

void Form_kccx::closeEvent(QCloseEvent *event)
{
    Q_UNUSED(event);
    QSettings setIni("clin.ini", QSettings::IniFormat);
    setIni.setValue("windowState/kccx", QVariant(this->geometry()));

    emit destroyW( "yk_kccx" );

}

void Form_kccx::on_pbExit_clicked()
{
    close();
}

void Form_kccx::on_pbFresh_clicked()
{
    //drug_dept_code,
    QString dept_code = "";
    if(ui->cbbStoreDept->currentText() != "全部")
    {
        dept_code = pub::getStr(store_depts, "^", pub::searchStr(store_depts, "^", ui->cbbStoreDept->currentText()) + 1);
    }
    QString sq;

    //日结查询
    if(ui->ckbDailyBack->isChecked())
    {
        if(!checkStatus(1))
        {
            return;
        }

        sq = "select dept_name 库房,to_char(back_date,'yyyy-mm-dd') 日期, drug_name 品名, specs 规格,pack_unit 单位, "
             " num_remain 截止数量, num_consume 日消耗, cur_retail_price 零售价 , "
             " (cur_retail_price * num_remain) 截止金额";
        sq += " from sun_drug_backup_daily ";
        sq += " left join com_department on dept_code = drug_dept_code ";
        sq += " where trunc(back_date) >=to_date('";
        sq += dt_begin + "','yyyy/mm/dd') and trunc(back_date) <= to_date('";
        sq += dt_end + "','yyyy/mm/dd') ";

        if(ui->ckbItem->isChecked())    //查询单个品种
        {
            if(ui->leName->text().isEmpty())
            {
                QMessageBox::information(this, "注意", "设置品种限定，但未指定品种信息！");
                return;
            }
            sq += " and drug_code = '" + drug_code +"'  ";
        }
        if(!dept_code.isEmpty())
        {
            sq += " and drug_dept_code = '" + dept_code +"'  ";
        }
        sq = sq + " order by back_date,dept_name, drug_name ";

        qDebug() <<"日结查询：" << sq;

        last_query_sql = sq;

        mdList1->setQuery(sq, pub::db_o);

        ui->tvList->setModel(mdList1);
        int width = ui->tvList->horizontalHeader()->width();
        pub::setColumnWidth(ui->tvList, "0.1^0.1^0.15^0.1^0.1^0.1^0.1^0.1^0.1^0^0", width, 11);

        return;
    }

    //
    if(ui->rbItem->isChecked()) //按品规划分品种
    {
        sq = "select (SELECT NVL (position, '-') FROM drug_base_info WHERE "
             " drug_code = code AND ROWNUM = 1 ) 柜号,"
             " 库房名称, 药品名称, 规格, 包装单位, 包装数量 from "
             " (select max(sto.drug_code) code ,"
             " (select dept_name from COM_DEPARTMENT where DEPT_CODE = STO.drug_dept_code and ROWNUM = 1) 库房名称,"
             " sto.trade_name 药品名称, "
             " sto.specs 规格, sto.pack_unit 包装单位, sum(store_sum / pack_qty) 包装数量, drug_dept_code";
        sq += " FROM pha_com_storage sto WHERE 1 = 1 ";//STO.drug_dept_code,sto.drug_code 药品编码,
    }else if(ui->rbFactory->isChecked())    // 按厂家划分
    {
        sq = "SELECT 柜号, 库房名称, 药品名称, 规格, 生产厂家, 包装单位, 账面数量, 挂账, 账面数量 - 挂账 实际数量,";
        sq += "CASE WHEN (whole_package_num = 0 OR whole_package_num IS NULL) THEN 0"
              " ELSE FLOOR ((账面数量 - 挂账) / whole_package_num) END 件数,"
              " CASE WHEN (whole_package_num = 0 OR whole_package_num IS NULL) THEN 账面数量 - 挂账"
              " ELSE MOD (账面数量 - 挂账, whole_package_num )END 零数 "
              " from "
              " (SELECT 柜号, 库房名称, 药品名称, 规格, 生产厂家, 包装单位, 账面数量,"
              " ( CASE WHEN 挂账数量 IS NULL THEN 0 ELSE 挂账数量 END ) 挂账,"
              " (CASE WHEN whole_package_num IS NULL THEN 0 ELSE whole_package_num END ) whole_package_num"
              " FROM "
              " (SELECT ( SELECT NVL (position, '-') FROM drug_base_info WHERE dept_code = ST.drug_dept_code "
              "AND drug_code = 药品编码  AND ROWNUM = 1 ) 柜号,"
              " 库房名称, 药品名称, 规格,包装单位, 账面数量,"
              " (SELECT fac_name FROM pha_com_company WHERE fac_code = PRODUCER_CODE AND ROWNUM = 1 ) 生产厂家,"
              " (SELECT NVL (package_num, 0) FROM drug_base_info WHERE dept_code = ST.drug_dept_code "
              "AND drug_code = 药品编码 AND ROWNUM = 1 ) whole_package_num,"
              " (SELECT NVL (recal, 0) FROM drug_base_info "
              " WHERE drug_code = 药品编码 and dept_code = ST.drug_dept_code AND ROWNUM = 1 ) 挂账数量"
              " FROM"
              " (SELECT (SELECT dept_name FROM COM_DEPARTMENT WHERE DEPT_CODE = STO.drug_dept_code AND ROWNUM = 1 ) 库房名称,"
              " sto.trade_name 药品名称,sto.specs 规格, max(STO.PRODUCER_CODE) PRODUCER_CODE, sto.pack_unit 包装单位,"
              " SUM (store_sum / pack_qty) 账面数量, sto.drug_code 药品编码, STO.drug_dept_code"
              " FROM pha_com_storage sto WHERE 1 = 1";

    }else  //按批号划分
    {
        sq = "SELECT  (SELECT NVL (position, '-') FROM drug_base_info WHERE "
             " drug_code = 药品编码 AND ROWNUM = 1 ) 柜号,"
             "库房名称, 药品名称, 规格, ";
        sq += " ( SELECT fac_name FROM pha_com_company WHERE fac_code = PRODUCER_CODE AND ROWNUM = 1 ) 生产厂家,";
        sq += "批号,  生产日期,  效期, 包装单位, 包装数量 FROM ";
        sq += " ( SELECT  (  SELECT  dept_name FROM COM_DEPARTMENT  WHERE  DEPT_CODE = STO.drug_dept_code";
        sq += " AND ROWNUM = 1 ) 库房名称, sto.trade_name 药品名称,  sto.specs 规格,  STO.PRODUCER_CODE,";
        sq += "batch_no 批号, manu_date 生产日期, valid_date 效期,";
        sq += " sto.pack_unit 包装单位, SUM (store_sum / pack_qty) 包装数量,sto.drug_code 药品编码";
        sq += " FROM pha_com_storage sto WHERE 1 = 1 ";

    }
    if(ui->ckbNo0->isChecked())
    {
        sq += " and sto.store_sum > 0 ";
    }
    if(!dept_code.isEmpty())
    {
        sq += " and sto.drug_dept_code = '" +  dept_code + "'";
    }
    if(ui->ckbItem->isChecked())    //查询单个品种
    {
        if(ui->leName->text().isEmpty())
        {
            QMessageBox::information(this, "注意", "设置品种限定，但未指定品种信息！");
            return;
        }
        if(ui->rbItem->isChecked()) //按品规
        {
            sq += " and sto.trade_name = '" +  ui->leName->text() + "'";
            sq += " and sto.specs = '" +  ui->leSpecs->text() + "'";
        }
        else if(ui->rbFactory->isChecked()) //按厂家
        {
            sq += " and sto.drug_code = '" +  drug_code + "'";
        }
        else  //批号
        {
            sq += " and sto.drug_code = '" +  drug_code + "'";
            sq += " and sto.batch_no like '%" + ui->leBatch->text() + "%'";
        }
    }

    if(ui->rbItem->isChecked())
    {
        sq += " group by STO.drug_dept_code, sto.trade_name , sto.specs , sto.pack_unit )";//, sto.drug_code ,
        sq += " order by 柜号 ";
    }
    else if(ui->rbFactory->isChecked())
    {
        sq += " group by STO.drug_dept_code, sto.trade_name, sto.specs, sto.pack_unit,  sto.drug_code) st )) ";// ,STO.PRODUCER_CODE,
        sq += " ORDER BY 柜号";
    }
    else
    {
        sq += " group by STO.drug_dept_code, sto.trade_name, sto.specs, sto.pack_unit, STO.PRODUCER_CODE, "
              "sto.drug_code, batch_no, manu_date, valid_date)";// ,
        sq += " ORDER BY  柜号, 药品名称, 批号";

    }

    qDebug()<<"提取库存清单："<<sq;

    last_query_sql = sq;

    mdList1->setQuery(sq, pub::db_o);

    ui->tvList->setModel(mdList1);
    int width = ui->tvList->horizontalHeader()->width();
    pub::setColumnWidth(ui->tvList, "0.06^0.06^0.15^0.1^0.15^0.06^0.06^0.06^0.06^0.06^0.06", width, 11);

}

void Form_kccx::on_tvList_clicked(const QModelIndex &index)
{


}

void Form_kccx::on_pbSearch_clicked()
{
    QStringList searchResult;
    searchResult = getSearch(ui->cbbMode->currentText(), ui->leKey->text().trimmed());

    if(searchResult.size() == 0)
    {
        return;
    }
    else
    {
        ui->leName->setText(searchResult[0]);
        ui->leSpecs->setText(searchResult[1]);
        ui->leFactory->setText(searchResult[2]);
        ui->leBatch->setText(searchResult[3]);
        drug_code = searchResult[4];
        factory_code = searchResult[5];

        ui->leHisCode->setText(drug_code);
        qro->exec("select med_list_codg from fin_com_compare where fixmedins_hilist_id = '" + drug_code + "'");
        qro->next();
        ui->leInsuCode->setText(qro->value(0).toString());

    }

    //设置间品种查询状态
    ui->ckbItem->setChecked(true);
}

QStringList Form_kccx::getSearch(QString mode, QString key_word)
{
    QStringList ret;
    sq = "SELECT distinct BASE.trade_name 药品名称, BASE.specs 规格,";
    if(mode == "厂家")
    {
        sq += " (SELECT fac_name FROM pha_com_company WHERE fac_code = base.PRODUCER_CODE AND ROWNUM = 1 ) 生产厂家,";
        sq += "base.PRODUCER_CODE, base.drug_code, ";
        sq += "''  from pha_com_baseinfo base where valid_state = '1' "
              " and ( spell_code like '%" + key_word.toUpper();
        sq += "%' or trade_name like '%" + key_word + "%')";
    }
    else if(mode == "批号")
    {
        sq += " (SELECT fac_name FROM pha_com_company WHERE fac_code = base.PRODUCER_CODE AND ROWNUM = 1 ) 生产厂家,";
        sq += "base.PRODUCER_CODE, base.drug_code, ";
        sq += " STO.BATCH_NO 批号";  //, STO.GROUP_CODE
        sq += " FROM pha_com_baseinfo base, PHA_COM_STORAGE sto ";
        sq += " WHERE BASE.DRUG_CODE = STO.DRUG_CODE ";
        sq += " and BASE.valid_state = '1'";
        sq += " and ( BASE.spell_code like '%" + key_word.toUpper();
        sq += "%' or BASE.trade_name like '%" + key_word + "%')";

        if((ui->ckbNo0->isChecked() && ui->tabWidget->currentIndex() == 0)
                ||(ui->ckb_none_0_2->isChecked() && ui->tabWidget->currentIndex() == 1))
        {
            sq += " and sto.store_sum > 0 ";
        }

    }
    else if(mode == "品规")
    {
        sq += " '' 生产厂家, '' PRODUCER_CODE, '' drug_code, '' 批号";
        sq += " FROM pha_com_baseinfo base ";
        sq += " WHERE BASE.valid_state = '1'";
        sq += " and ( BASE.spell_code like '%" + key_word.toUpper();
        sq += "%' or BASE.trade_name like '%" + key_word + "%')";
    }
    else
    {
        QMessageBox::information(this, "注意", "检索模式无效！");
        return ret;
    }
    sq += " order by base.trade_name, base.specs, producer_code";

    if(mode == "批号")
    {
        sq += ", batch_no";
    }

    qDebug()<<" select drug item : "<<sq;

    mdSearchPop->setQuery( sq , pub::db_o);

    if(mdSearchPop->rowCount() == 0)
    {
        QMessageBox::information(this, "注意", "指定关键字找不到有效的项目！");
        return ret;
    }
    else if(mdSearchPop->rowCount() == 1)
    {
        ret << mdSearchPop->record(0).value(0).toString();
        ret << mdSearchPop->record(0).value(1).toString();
        ret << mdSearchPop->record(0).value(2).toString();
        ret << mdSearchPop->record(0).value(5).toString();
        ret << mdSearchPop->record(0).value(4).toString();
        ret << mdSearchPop->record(0).value(3).toString();

        return ret;
    }
    else
    {
        if(mode == "厂家")
        {
            dlgGetItem->setDisplay(*mdSearchPop,QRect(200, 200, 800, 500),"","0.3^0.2^0.3^0^0^0^0^0^0");
        }
        else if(mode == "批号")
        {
            dlgGetItem->setDisplay(*mdSearchPop,QRect(200, 200, 800, 500),"","0.25^0.2^0.25^0^0^0.2^0^0^0");
        }
        else
        {
            dlgGetItem->setDisplay(*mdSearchPop,QRect(200, 200, 800, 500),"","0.3^0.2^0^0^0^0^0^0^0");
        }

        dlgGetItem->setModal(true);
        dlgGetItem->exec();

        QString popRetStr = dlgGetItem->retStr();
        if(popRetStr.isEmpty())
        {
            return ret;
        }
        qDebug()<<"pop select return value is : "<<popRetStr;

        ret << pub::getStr(popRetStr, "^", 1);
        ret << pub::getStr(popRetStr, "^", 2);
        ret << pub::getStr(popRetStr, "^", 3);
        ret << pub::getStr(popRetStr, "^", 6);
        ret << pub::getStr(popRetStr, "^", 5);
        ret << pub::getStr(popRetStr, "^", 4);

        return ret;

    }
}

void Form_kccx::on_leKey_returnPressed()
{
    on_pbSearch_clicked();
}

void Form_kccx::on_pbFresh_3_clicked()
{
    if(!checkStatus(3))
    {
        return;
    }

    if(ui->cbbRelate->currentText().isEmpty())
    {
        QMessageBox::information(this,"注意","未指定查询的往来关系！");
        return;
    }

    QString relacd = "";
    if(ui->cbbRelate->currentText() != "全部")
    {
        relacd = pub::getStr(relate_name_code, "^", pub::searchStr(relate_name_code, "^", ui->cbbRelate->currentText()) + 1);
        if(ui->ckbIn_3->isChecked())
        {
            //入库
            relacd = " and company_code = '" + relacd + "' ";
        }
        else
        {
            //出
            relacd = " and drug_storage_code = '" + relacd + "' ";
        }
    }

    int width = ui->tvList_3->horizontalHeader()->width(); //列表宽度

    if(ui->ckbIn_3->isChecked())
    {
        //入库

        if(ui->ckbItemName->isChecked())
        {
            //按药名汇总
            sq = " select trade_name 药品名称, drug_code 药品编码, max(specs) 规格, sum(in_num / PACK_QTY) 数量,";
            sq += " max(pack_unit) 单位, sum(purchase_price * in_num / PACK_QTY) 金额 ";
            sq += " FROM PHA_COM_INPUT ";
            sq += " where drug_dept_code = '" + current_dept_code + "' " + dateRangeIn + relacd ;
            sq += " group by drug_code, trade_name ";
            sq += " order by trade_name ";
        }
        else
        {
            //品种明细
            sq = "SELECT TO_CHAR (in_date, 'yyyy-mm-dd') 日期, com_priv_class3.class3_name 入库类型, f_c.fac_name 供货商,"
                 " trade_name 药品名称, drug_code 药品编码, specs 规格, purchase_price 购入价格,"
                 " in_num / PACK_QTY 数量, pack_unit 单位, batch_no 批号, valid_date 效期, pay_state 付款状态 "
                 " FROM PHA_COM_INPUT "
                 " LEFT JOIN PHA_COM_COMPANY f_c ON f_c.FAC_CODE = COMPANY_CODE "
                 " LEFT JOIN com_priv_class3 ON com_priv_class3.class3_code = in_type AND com_priv_class3.class2_code = '0310'"
                 " where drug_dept_code = '" + current_dept_code + "' " + dateRangeIn  + relacd;
            sq += " order by in_date";

        }

        qDebug()<<"提取入库汇总："<<sq;

        mdList3->setQuery(sq, pub::db_o);

        ui->tvList_3->setModel(mdList3);
        //int width = ui->tvList_3->horizontalHeader()->width();
        //pub::setColumnWidth(ui->tvList_3, "0.08^0.08^0.12^0.12^0.1^0.06^0.06^0.06^0.06^0.08^0.08^0.06^0^0^0^0^0", width, 15);

        if(ui->ckbAmount->isChecked())
        {
            //金额汇总
            sq = "SELECT f_c.fac_name 供货商, sum(purchase_price * in_num / PACK_QTY) 金额 "
                 " FROM PHA_COM_INPUT "
                 " LEFT JOIN PHA_COM_COMPANY f_c ON f_c.FAC_CODE = COMPANY_CODE "
                 " where drug_dept_code = '" + current_dept_code + "' " + dateRangeIn  + relacd;
            sq += " group by f_c.fac_name order by f_c.fac_name ";

            qDebug() << "入库金额汇总：" << sq;

            mdList4->setQuery(sq, pub::db_o);

            ui->tvList_4->setModel(mdList4);
            pub::setColumnWidth(ui->tvList_4, "0.2^0.2^0^0^0^0^0", width, 5);
        }
        else
        {
            ui->tvList_4->setModel(NULL);
        }
    }

    if(ui->ckbOut_3->isChecked())
    {
        //出库

        if(ui->ckbItemName->isChecked())
        {
            //按药名汇总
            sq = " select trade_name 药品名称, drug_code 药品编码, max(specs) 规格, sum(out_num / PACK_QTY) 数量,";
            sq += " max(pack_unit) 单位, sum(sale_cost) 金额 ";//purchase_price * out_num / PACK_QTY
            sq += " FROM PHA_COM_OUTPUT ";
            sq += " where drug_dept_code = '" + current_dept_code + "' " + dateRangeOut + relacd;
            sq += " group by drug_code, trade_name  ";
            sq += " order by trade_name ";
        }
        else
        {
            //明细
            sq = " SELECT TO_CHAR (out_date, 'yyyy-mm-dd') 日期, com_priv_class3.class3_name 出库类型, C_D.DEPT_NAME 出库对象,"
                 " trade_name 药品名称, drug_code 药品编码, specs 规格, purchase_price 购入价格,"
                 " out_num / PACK_QTY 数量, pack_unit 单位, batch_no 批号, to_char(valid_date,'yyyy-mm-dd') 效期,"
                 " PRODUCER_CODE 生产商, COMPANY_CODE, DRUG_STORAGE_CODE"
                 " FROM PHA_COM_OUTPUT "
                 " LEFT JOIN COM_DEPARTMENT C_D ON C_D.DEPT_CODE = DRUG_STORAGE_CODE and ROWNUM = 1 "
                 " LEFT JOIN com_priv_class3 ON com_priv_class3.class3_code = out_type AND com_priv_class3.class2_code = '0320'"
                 "  where drug_dept_code = '" + current_dept_code + "' " + dateRangeOut + relacd;
            sq += " order by out_date";

        }

        qDebug()<<"提取出库汇总："<<sq;
        mdList3->setQuery(sq, pub::db_o);

        ui->tvList_3->setModel(mdList3);
        //int width = ui->tvList_3->horizontalHeader()->width();
        //pub::setColumnWidth(ui->tvList_3, "0.3^0.2^0.1^0.1^0.1^0.1^0^0^0^0^0^0^0^0^0^0", width, 15);


        if(ui->ckbAmount->isChecked())
        {
            //金额汇总
            sq = " SELECT C_D.DEPT_NAME 出库对象, to_char(ROUND(sum(purchase_price * out_num / PACK_QTY ),2)） 购入金额 ,"
                 " to_char(round(sum(retail_price * out_num / PACK_QTY ),2)) 销售金额 "
                 " FROM PHA_COM_OUTPUT "
                 " LEFT JOIN COM_DEPARTMENT C_D ON C_D.DEPT_CODE = DRUG_STORAGE_CODE and ROWNUM = 1 "
                 "  where drug_dept_code = '" + current_dept_code + "' " + dateRangeOut  + relacd;

            sq += " group by C_D.DEPT_NAME order by C_D.DEPT_NAME";

            qDebug()<<"出库金额汇总："<<sq;
            mdList4->setQuery(sq, pub::db_o);

            ui->tvList_4->setModel(mdList4);
            pub::setColumnWidth(ui->tvList_4, "0.2^0.2^0.2^0^0^0^0", width, 5);
        }
        else
        {
            ui->tvList_4->setModel(NULL);
        }
    }

    if(ui->ckbItemName->isChecked())
    {
        pub::setColumnWidth(ui->tvList_3, "0.3^0.2^0.1^0.1^0.1^0.1^0^0^0^0^0^0^0^0^0^0", width, 15);
    }
    else
    {
        pub::setColumnWidth(ui->tvList_3, "0.08^0.08^0.12^0.12^0.1^0.06^0.06^0.06^0.06^0.08^0.08^0.06^0^0^0^0^0", width, 15);
    }

}

void Form_kccx::on_pbFresh_2_clicked()
{
    if(!checkStatus(2))
    {
        return;
    }

    int pos;
    //库存类型, CLASS3_MEANING_CODE
    pos = pub::searchStr(store_depts, "^", ui->cbbStoreDept->currentText());
    QString store_type = pub::getStr(store_depts, "^", pos + 2);

    QString tmp_sq_fld_in, tmp_sq_fld_out, tmp_sq_tab_out;
    if(store_type == "P")
    {
        //药房
        tmp_sq_fld_in = " '' 患者姓名, '', '' 病志号, '' 医生, ";
        tmp_sq_fld_out = " reci.patient_name, reci.doct_code, reci.card_no, empl.empl_name, ";
        tmp_sq_tab_out = " LEFT JOIN PHA_STO_RECIPE reci on reci.recipe_no = outp.recipe_no "
                         " LEFT JOIN COM_EMPLOYEE empl on empl.empl_code = reci.doct_code ";
    }


    sq = " select to_char(in_date ,'yyyy-mm-dd') 日期, com_priv_class3.class3_name 入出类型,"
         " in_num / pack_qty 变更数量, store_num /pack_qty 结存数量, pack_unit 单位,purchase_price 价格,"
         " purchase_price * in_num/pack_qty  金额 , batch_no 批号, TO_CHAR(valid_date,'yyyy-mm-dd') 效期, "
         " f_c.fac_name 供货商, (case when in_state = '2' then '核准' else '待确认' end) 状态 , "
         " %1 "
         " in_date ord_date, trade_name, specs "
         " from pha_com_input pha_in "
         " left JOIN PHA_COM_COMPANY f_c on f_c.FAC_CODE = pha_in.company_CODE "
         " left JOIN com_priv_class3 on com_priv_class3.class3_code = in_type and com_priv_class3.class2_code = '0310' "
         " where rownum < 10000 and drug_dept_code = '" + current_dept_code + "' ";

    if(!ui->ckbIn_2->isChecked())
    {
        //不查看入库记录, 设置入库常非条件
        sq += " and 1 > 2 ";
    }
    else
    {
        if(ui->cbbMode->currentText() == "品规")// rbItem_2->isChecked())
        {
            sq += " and trade_name = '" + ui->leName_2->text() + "'";
            sq += " and specs = '" + ui->leSpecs_2->text() + "'";
        }
        else if(ui->cbbMode->currentText() == "厂家")//rbFactory_2->isChecked())
        {
            sq += " and drug_code = '" + drug_code + "'";

        }
        else if(ui->cbbMode->currentText() == "批号")// Batch_2->isChecked())
        {
            sq += " and drug_code = '" + drug_code + "'";
            sq += " and batch_no = '" + ui->leBatch_2->text() + "'";
        }
        else
        {
            QMessageBox::information(this,"注意","未指定查询条件！");
            return;
        }
        sq += dateRangeIn ;
    }

    sq += " union select "
          " TO_CHAR (outp.out_date, 'yyyy-mm-dd hh24:mi:ss') 日期, com_priv_class3.class3_name 入出类型,"
          " outp.out_num / outp.pack_qty 变更数量, outp.store_num / outp.pack_qty  结存数量, "
          " outp.pack_unit 单位, outp.purchase_price 价格,"
          " outp.purchase_price * outp.out_num / outp.pack_qty 金额, outp.batch_no 批号,"
          " TO_CHAR (outp.valid_date, 'yyyy-mm-dd') 效期,"
          " f_c.fac_name 供货商,(CASE WHEN outp.out_state = '2' THEN '核准' ELSE '待确认' END ) 状态, "
          " %2 "
          " outp.out_date ord_date , outp.trade_name , outp.specs "
          " FROM pha_com_output outp "
          " LEFT JOIN PHA_COM_COMPANY f_c ON f_c.FAC_CODE = company_CODE "
          " LEFT JOIN com_priv_class3 ON com_priv_class3.class3_code = out_type "
          " AND com_priv_class3.class2_code = '0320' "
          " %3 "
          " where outp.drug_dept_code = '" + current_dept_code + "' ";

    if(!ui->ckbOut_2->isChecked())
    {
        sq += " and 1> 2 ";  //不查看出库记录, 设置出库常非条件
        //tmp_sq_fld_in = "";  //不查看出库记录,入库相关字段不需要填充
    }
    else
    {
        if(ui->cbbMode->currentText() == "品规")// rbItem_2->isChecked())
        {
            sq += " and outp.trade_name = '" + ui->leName_2->text() + "'";
            sq += " and outp.specs = '" + ui->leSpecs_2->text() + "'";
        }
        else if(ui->cbbMode->currentText() == "厂家")// rbFactory_2->isChecked())
        {
            sq += " and outp.drug_code = '" + drug_code + "'";

        }
        else if(ui->cbbMode->currentText() == "批号")// rbBatch_2->isChecked())
        {
            sq += " and outp.drug_code = '" + drug_code + "'";
            sq += " and outp.batch_no = '" + ui->leBatch_2->text() + "'";
        }
        else
        {
            QMessageBox::information(this,"注意","未指定查询条件！");
            return;
        }

        sq += dateRangeOut ;
    }

    sq += "  order by ord_date";

    sq = sq.arg(tmp_sq_fld_in).arg(tmp_sq_fld_out).arg(tmp_sq_tab_out);

    qDebug()<<"提取入库出库明细："<<sq;
    mdList2->setQuery(sq, pub::db_o);

    ui->tvList_2->setModel(mdList2);
    int width = ui->tvList_2->horizontalHeader()->width();
    if(ui->ckbOut_2->isChecked() && store_type == "P")
    {
        //药房，包含出库数据，增加患者姓名等显示列
        pub::setColumnWidth(ui->tvList_2, "0.08^0.08^0.08^0.08^0.08^0.08^0.08^0.08^0.08^0.08^0.06^0.08^0^0.08^0.06", width);
    }
    else
    {
        pub::setColumnWidth(ui->tvList_2, "0.08^0.08^0.08^0.08^0.08^0.08^0.08^0.08^0.08^0.15^0.06", width);
    }
}

void Form_kccx::on_pbRelate_clicked()
{

    if(!checkStatus(3))
    {
        return;
    }

    if(ui->ckbIn_3->isChecked())
    {
        sq = "select case when cname is null then dname else cname end, company_code from "
             " (select (select dept_name from com_department where dept_code = company_code and rownum = 1) dname, "
             "(SELECT fac_name FROM pha_com_company WHERE fac_code = company_code AND ROWNUM = 1 ) cname, company_code"
             " from (select distinct company_code from pha_com_input where 1 = 1 " +dateRangeIn ;
        sq += " and drug_dept_code = '" + current_dept_code + "'))";

        qDebug()<<"检索往来关系："<<sq;
        relate_name_code = pub::fillCombo(ui->cbbRelate, sq, 0, qro);
        if(relate_name_code.isEmpty())
        {
            QMessageBox::information(this,"注意","指定时间范围找不到入库数据！");
            return;
        }
        ui->cbbRelate->addItem("全部");

    }
    else //if(ui->ckbOut->isChecked())
    {
        sq = " select (select dept_name from com_department where dept_code = drug_storage_code and rownum = 1) dname, "
             "  drug_storage_code"
             " from (select distinct drug_storage_code from pha_com_output where 1 = 1 " +dateRangeOut ;
        sq += " and drug_dept_code = '" + current_dept_code + "')";

        qDebug()<<"检索往来关系："<<sq;
        relate_name_code = pub::fillCombo(ui->cbbRelate, sq, 0, qro);
        if(relate_name_code.isEmpty())
        {
            QMessageBox::information(this,"注意","指定时间范围找不到入库数据！");
            return;
        }
        ui->cbbRelate->addItem("全部");

    }
}

bool Form_kccx::checkStatus(int mode)
{

    QDate dt1, dt2;
    dt1 = ui->deBegin->date();
    dt2 = ui->deEnd->date();
    if(dt1 > dt2)
    {
        QMessageBox::information(this,"注意","指定的时间范围无效！");
        return false;
    }

    if(dt2 > dt1.addDays(365))
    {
        QMessageBox::information(this,"注意","查询时间范围不允许超过 1 年！");
        return false;
    }

    dt_begin = dt1.toString("yyyy-MM-dd");
    dt_end = dt2.toString("yyyy-MM-dd");

    dateRangeIn = " and trunc(in_date) >= to_date('" + dt_begin + "','yyyy-mm-dd') ";
    dateRangeIn += " and trunc(in_date) <= to_date('" + dt_end + "','yyyy-mm-dd') ";
    dateRangeOut = " and trunc(out_date) >= to_date('" + dt_begin + "','yyyy-mm-dd') ";
    dateRangeOut += " and trunc(out_date) <= to_date('" + dt_end + "','yyyy-mm-dd') ";

    current_dept_code = pub::getStr(store_depts, "^", pub::searchStr(store_depts, "^", ui->cbbStoreDept->currentText()) + 1);

    if(mode == 2)   //出入明细查询
    {
        if(!ui->ckbOut_2->isChecked() && !ui->ckbIn_2->isChecked())
        {
            QMessageBox::information(this, "注意", "入库查询或出库查询至少选定一项！");
            return false;
        }
        if(ui->cbbStoreDept->currentText() == "全部")
        {
            QMessageBox::information(this, "注意", "出入查询必须指定具体库房！");
            return false;
        }

        if(ui->leName_2->text().trimmed().isEmpty())
        {
            QMessageBox::information(this, "注意", "请指定具体品种！");
            return false;
        }

    }

    if(mode == 3)   //出入汇总查询
    {
        if(ui->cbbStoreDept->currentText() == "全部")
        {
            QMessageBox::information(this, "注意", "此项查询必须指定具体库房！");
            return false;
        }

        //        if(!ui->ckbOut_3->isChecked() && !ui->ckbIn_3->isChecked())
        //        {
        //            QMessageBox::information(this, "注意", "入库查询或出库查询至少选定一项！");
        //            return false;
        //        }

        //        if(ui->ckbIn_3->isChecked() && ui->ckbOut_3->isChecked())
        //        {
        //            QMessageBox::information(this, "注意", "不可以同时选择入库查询或出库查询！");
        //            return false;
        //        }

    }

    return true;
}

void Form_kccx::on_pbSearch_2_clicked()
{
    QStringList searchResult;
    searchResult = getSearch(ui->cbbMode_2->currentText(), ui->leKey_2->text().trimmed());

    if(searchResult.size() == 0)
    {
        return;
    }
    else
    {
        ui->leName_2->setText(searchResult[0]);
        ui->leSpecs_2->setText(searchResult[1]);
        ui->leFactory_2->setText(searchResult[2]);
        ui->leBatch_2->setText(searchResult[3]);
        drug_code = searchResult[4];
        factory_code = searchResult[5];
    }
}

void Form_kccx::on_leKey_2_returnPressed()
{
    on_pbSearch_2_clicked();
}

void Form_kccx::on_tabWidget_currentChanged(int index)
{
    if(index == 2)
    {
        ui->tvList_3->setModel(NULL);
        ui->tvList_4->setModel(NULL);
    }
    else if(index == 3 )
    {
        ui->tvList_fin->setModel(NULL);
    }
}

void Form_kccx::on_pbCost_clicked()
{

    if(!checkStatus(3))
    {
        return;
    }

    QString dept_type = pub::getStr(store_depts, "^", pub::searchStr(store_depts, "^", ui->cbbStoreDept->currentText()) + 2);
    if(dept_type != "P")
    {
        QMessageBox::information(this, "注意", "只有药房单位才可以生成成本表！");
        return ;
    }

    QDate dt1, dt2;
    dt1 = ui->deBegin->date();
    dt2 = ui->deEnd->date();
    if(dt2 > dt1.addDays(30))
    {
        QMessageBox::information(this, "注意", "日期范围跨度不合理！");
        return ;
    }

    sq = "select  TO_CHAR(out_date, 'yyyy-mm-dd') dt , sum(purchase_price * OUT_NUM / PACK_QTY) cost , drug_type "
         " from PHA_COM_OUTPUT "
         " where drug_dept_code = '" + current_dept_code + "' " + dateRangeOut;
    sq += " group by TO_CHAR(out_date, 'yyyy-mm-dd'), DRUG_TYPE";
    sq += " order by dt ";

    qDebug()<<"提取药品成本报表："<<sq;

    if(!qro->exec(sq))
    {
        QMessageBox::information(this, "注意", "提取药品成本报表发生错误 \n！" + qro->lastError().text());
        return ;
    }

    if(!qro->next())
    {
        QMessageBox::information(this, "注意", "指定时间范围无数据 ！" );
        return ;
    }

    QJsonDocument d;
    QJsonArray jsa_val;    //数值
    QJsonArray jsa_clr;    //预清除区域
    QJsonObject jot;  // 临时json
    int row = 6,  col;
    QString dt = "begin";
    bool isFirstChange = true;  //首次变化标志，用于控制循环

    qrm->exec("delete from tmp_drug_cost");
    double  xy , zy , zc , xya , zya , zca ;
    xy = zy = zc = xya = zya = zca = 0;
    //数据表相同 dt 数据在报表中为同一行数据，dt 变换之后报表进行换行
    while(true)
    {
        if(dt != qro->value("dt").toDate().toString("yyyy-MM-dd"))
        {
            //日期发生变化
            if(isFirstChange)
            {
                //首次进入循环
                isFirstChange = false;
            }
            else
            {
                sq = "insert into tmp_drug_cost (日期, 西药, 中成药, 中草药, 小计 ) values (";
                sq += "'" + dt + "',";
                sq += "'" + QString::number(xy, 'f', 2) + "',";
                sq += "'" + QString::number(zy, 'f', 2) + "',";
                sq += "'" + QString::number(zc, 'f', 2) + "',";
                sq += "'" + QString::number(xy + zy + zc) + "')";
                xya += xy;
                zya += zy;
                zca += zc;

                qrm->exec(sq);

                row ++ ; //日期变化，行数增加
            }

            dt = qro->value("dt").toString();   //新一行的日期
            //在表格中写入日期
            jot.insert("val", dt);
            jot.insert("row", row);
            jot.insert("col", 1);
            jsa_val << jot;
            xy = zy = zc = 0;
        }

        if(qro->value("drug_type").toString() == "P"){   //西药
            col = 2;
            xy = qro->value("cost").toDouble();
        }else if(qro->value("drug_type").toString() == "Z"){  //中成药
            col = 3;
            zy = qro->value("cost").toDouble();
        }else if(qro->value("drug_type").toString() == "C"){  //中草药
            col = 4;
            zc = qro->value("cost").toDouble();
        }else{
            QMessageBox::information(this, "注意", dt + " ， 药品类别无效 ！" );
            return ;
        }

        jot.insert("val", QString::number(qro->value("cost").toDouble(), 'f', 2));
        jot.insert("row", row);
        jot.insert("col", col);
        jsa_val << jot;

        if(!qro->next())
        {
            sq = "insert into tmp_drug_cost (日期, 西药, 中成药, 中草药, 小计 ) values (";
            sq += "'" + dt + "',";
            sq += "'" + QString::number(xy, 'f', 2) + "',";
            sq += "'" + QString::number(zy, 'f', 2) + "',";
            sq += "'" + QString::number(zc, 'f', 2) + "',";
            sq += "'" + QString::number(xy + zy + zc) + "')";
            xya += xy;
            zya += zy;
            zca += zc;

            qrm->exec(sq);
            break;
        }
    }

    jot.insert("val", "查询时间：" + dt1.toString("yyyy-MM-dd") + " 至 " + dt2.toString("yyyy-MM-dd"));
    jot.insert("row", 3);
    jot.insert("col", 1);
    jsa_val << jot;
    jot.insert("val", "打印时间：" + QDate::currentDate().toString("yyyy-MM-dd"));
    jot.insert("row", 3);
    jot.insert("col", 4);
    jsa_val << jot;


    sq = "insert into tmp_drug_cost (日期, 西药, 中成药, 中草药, 小计 ) values (";
    sq += "'合计',";
    sq += "'" + QString::number(xya, 'f', 2) + "',";
    sq += "'" + QString::number(zya, 'f', 2) + "',";
    sq += "'" + QString::number(zca, 'f', 2) + "',";
    sq += "'" + QString::number(xya + zya + zca, 'f', 2) + "')";

    qrm->exec(sq);

    sq = " select 日期, 西药, 中成药, 中草药, 小计 from tmp_drug_cost order by atinc";

    mdList3->setQuery(sq, pub::db_m);
    ui->tvList_fin->setModel(mdList3);
    int width = ui->tvList_fin->horizontalHeader()->width();
    pub::setColumnWidth(ui->tvList_fin, "0.15^0.15^0.15^0.15^0.15^0^0^0^0^0^0^0^0^0^0", width, 15);

    QMessageBox::StandardButton result;
    result = QMessageBox::warning(NULL, "注意", "报表生成完毕，是否打印输出？",QMessageBox::Yes|QMessageBox::No);
    if(result == QMessageBox::No){
        return;
    }

    jot = d.object();
    jot.insert("row1",6);
    jot.insert("row2",37);
    jot.insert("col1",1);
    jot.insert("col2",4);

    jsa_clr << jot;

    QJsonObject jo; //打印参数
    jo.insert("excel_model", "/sheets/yaopinchengben.xls");
    jo.insert("cell_value", jsa_val);
    jo.insert("clear_range", jsa_clr);

    pub::printOutExcel(jo);

}



void Form_kccx::on_pbSelfDrug_clicked()
{
    if(!checkStatus(3))
    {
        return;
    }

    QString dept_type = pub::getStr(store_depts, "^", pub::searchStr(store_depts, "^", ui->cbbStoreDept->currentText()) + 2);
    if(dept_type != "P")
    {
        QMessageBox::information(this, "注意", "只有药房单位才可以生成报表！");
        return ;
    }

    sq = " select b.department 用药诊室, o.drug_type 药品类别, o.TRADE_NAME 药品名称, o.SPECS 规格, "
         " PACK_UNIT 单位, sum(OUT_NUM / PACK_QTY) 数量, purchase_price 进价, retail_price 零售价, "
         " sum(approve_cost) 进价总额, sum(sale_cost) 零售总额, sum(sale_cost - approve_cost) 差价 "
         " from PHA_COM_OUTPUT o "
         " join DRUG_BASE_INFO b on b.drug_code = o.drug_code and b.COME_FROM = '制剂' "
         " where DRUG_DEPT_CODE = '" + current_dept_code + "' " + dateRangeOut;
    sq += " group by o.TRADE_NAME, o.SPECS, PACK_UNIT, o.drug_type, b.department, purchase_price , retail_price"
          " order by b.DEPARTMENT,o.drug_type, o.TRADE_NAME";

    qDebug()<<"自制药品消耗统计："<<sq;

    mdList3->setQuery(sq, pub::db_o);
    ui->tvList_fin->setModel(mdList3);
    int width = ui->tvList_fin->horizontalHeader()->width();
    pub::setColumnWidth(ui->tvList_fin, "0.08^0.08^0.15^0.1^0.05^0.08^0.08^0.08^0.08^0.08^0.08^0^0^0^0^0^0", width, 15);

    if(!qro->exec(sq))
    {
        QMessageBox::information(this, "注意", "提取自制药品消耗报表发生错误 \n！" + qro->lastError().text());
        return ;
    }

    if(!qro->next())
    {
        QMessageBox::information(this, "注意", "指定时间范围无数据 ！" );
        return ;
    }

    QJsonDocument d;
    QJsonArray jsa_val;    //数值
    QJsonArray jsa_clr;    //预清除区域
    QJsonObject jot;  // 临时json
    int row = 4;
    QString key = "begin";
    bool isFirstChange = true;  //首次变化标志，用于控制循环
    QString drug_type;  //药品类别，P 西药， Z中成药， C中草药
    QString drug_consume_dept;  //用药诊室

    double  purchase_cost , sale_cost , diff_cost, purchase_cost_a , sale_cost_a , diff_cost_a ;
    purchase_cost = sale_cost = diff_cost = purchase_cost_a = sale_cost_a = diff_cost_a  = 0;

    //数据表每一行数据对应报表一行，key变换之后进行一次小结
    while(true)
    {
        if(key != qro->value("用药诊室").toString() + qro->value("药品类别").toString())
        {
            //key发生变化
            if(isFirstChange)
            {
                //首次进入循环
                isFirstChange = false;
            }
            else
            {

                jot.insert("val", drug_consume_dept + "-" + drug_type + " 小计");
                jot.insert("row", row);
                jot.insert("col", 1);
                jsa_val << jot;

                jot.insert("val", QString::number(purchase_cost_a, 'f', 2));
                jot.insert("row", row);
                jot.insert("col", 6);
                jsa_val << jot;

                jot.insert("val", QString::number(sale_cost_a, 'f', 2));
                jot.insert("row", row);
                jot.insert("col", 7);
                jsa_val << jot;

                jot.insert("val", QString::number(diff_cost_a, 'f', 2));
                jot.insert("row", row);
                jot.insert("col", 8);
                jsa_val << jot;

                purchase_cost_a = sale_cost_a = diff_cost_a = 0;

                row ++ ; //行数增加
            }
            key = qro->value("用药诊室").toString() + qro->value("药品类别").toString();   //新一行
        }

        jot.insert("val", qro->value("药品名称").toString());
        jot.insert("row", row);
        jot.insert("col", 1);
        jsa_val << jot;

        jot.insert("val", qro->value("单位").toString());
        jot.insert("row", row);
        jot.insert("col", 2);
        jsa_val << jot;

        jot.insert("val", QString::number(qro->value("数量").toDouble(), 'f', 2));
        jot.insert("row", row);
        jot.insert("col", 3);
        jsa_val << jot;

        jot.insert("val", QString::number(qro->value("进价").toDouble(), 'f', 2));
        jot.insert("row", row);
        jot.insert("col", 4);
        jsa_val << jot;

        jot.insert("val", QString::number(qro->value("零售价").toDouble(), 'f', 2));
        jot.insert("row", row);
        jot.insert("col", 5);
        jsa_val << jot;

        jot.insert("val", QString::number(qro->value("进价总额").toDouble(), 'f', 2));
        jot.insert("row", row);
        jot.insert("col", 6);
        purchase_cost = qro->value("进价总额").toDouble();
        purchase_cost_a += purchase_cost;
        jsa_val << jot;

        jot.insert("val", QString::number(qro->value("零售总额").toDouble(), 'f', 2));
        jot.insert("row", row);
        jot.insert("col", 7);
        sale_cost = qro->value("零售总额").toDouble();
        sale_cost_a += sale_cost;
        jsa_val << jot;

        jot.insert("val", QString::number(qro->value("差价").toDouble(), 'f', 2));
        jot.insert("row", row);
        jot.insert("col", 8);
        diff_cost = qro->value("差价").toDouble();
        diff_cost_a += diff_cost;
        jsa_val << jot;

        drug_type = qro->value("药品类别").toString();
        if(drug_type == "P")
        {
            drug_type = "西药";
        }else if(drug_type == "Z")
        {
            drug_type = "中成药";
        }if(drug_type == "C")
        {
            drug_type = "中草药";
        }

        drug_consume_dept = qro->value("用药诊室").toString().trimmed();

        row ++ ; //行数增加
        if(!qro->next())
        {

            jot.insert("val", drug_consume_dept + "-" + drug_type + " 小计");
            jot.insert("row", row);
            jot.insert("col", 1);
            jsa_val << jot;

            jot.insert("val", QString::number(purchase_cost_a, 'f', 2));
            jot.insert("row", row);
            jot.insert("col", 6);
            jsa_val << jot;

            jot.insert("val", QString::number(sale_cost_a, 'f', 2));
            jot.insert("row", row);
            jot.insert("col", 7);
            jsa_val << jot;

            jot.insert("val", QString::number(diff_cost_a, 'f', 2));
            jot.insert("row", row);
            jot.insert("col", 8);
            jsa_val << jot;

            row ++ ; //行数增加

            break;
        }
    }

    jot.insert("val", "查询时间：" + dt_begin + " 至 " + dt_end);
    jot.insert("row", 2);
    jot.insert("col", 1);
    jsa_val << jot;

    jot.insert("val", "打印时间：" + QDate::currentDate().toString("yyyy-MM-dd"));
    jot.insert("row", 2);
    jot.insert("col", 6);
    jsa_val << jot;

    QMessageBox::StandardButton result;
    result = QMessageBox::warning(NULL, "注意", "报表生成完毕，是否打印输出？",QMessageBox::Yes|QMessageBox::No);
    if(result == QMessageBox::No){
        return;
    }

    jot = d.object();
    jot.insert("row1",4);
    jot.insert("row2",25);
    jot.insert("col1",1);
    jot.insert("col2",8);
    jsa_clr << jot;

    QJsonObject jo; //打印参数
    jo.insert("excel_model", "/sheets/zhijiyaopin.xls");
    jo.insert("cell_value", jsa_val);
    jo.insert("clear_range", jsa_clr);

    pub::printOutExcel(jo);

}

void Form_kccx::on_pbInOut_clicked()
{

    if(!checkStatus(3))
    {
        return;
    }

    sq = "select base.drug_code 药品编码, base.drug_name 药品名称, base.specs 规格, base.pack_unit 包装单位,"
         " BEGIN.num_remain 上期结余, income.in_num 本期收入, base.NUM_CONSUME 本期消耗, END.num_remain 本期结余 "
         " from ( "
         " select drug_code, drug_name, specs, pack_unit ,sum(NUM_CONSUME) NUM_CONSUME "
         " from SUN_DRUG_BACKUP_DAILY where "
         " DRUG_DEPT_CODE = '%1'  and"
         " trunc(BACK_DATE) >= to_date('%2','yyyy-mm-dd')"
         " and trunc(BACK_DATE) <= to_date('%3','yyyy-mm-dd')"
         " group by drug_code, drug_name, specs,pack_unit ) base"
         " left join (select drug_code, num_remain from SUN_DRUG_BACKUP_DAILY where "
         " DRUG_DEPT_CODE = '%1'  and "
         " trunc(BACK_DATE) = to_date('%2','yyyy-mm-dd') -1 ) begin on BEGIN.drug_code = BASE.DRUG_CODE "
         " left join (select drug_code, num_remain from SUN_DRUG_BACKUP_DAILY where "
         " DRUG_DEPT_CODE = '%1'  and "
         " trunc(BACK_DATE) = to_date('%3','yyyy-mm-dd')) end on end.drug_code = BASE.DRUG_CODE"
         " LEFT JOIN(select drug_code, sum(in_num/pack_qty) in_num from PHA_COM_INPUT where "
         " DRUG_DEPT_CODE = '%1'  and "
         " trunc(in_DATE) >= to_date('%2','yyyy-mm-dd') "
         " and trunc(in_DATE) <= to_date('%3','yyyy-mm-dd') "
         " group by drug_code) income on income.drug_code = BASE.DRUG_CODE "
         " order by base.drug_name";

    sq = sq.arg(current_dept_code).arg(dt_begin).arg(dt_end);

    qDebug()<<"阶段药品汇总："<<sq;

    mdList3->setQuery(sq, pub::db_o);
    ui->tvList_fin->setModel(mdList3);
    int width = ui->tvList_fin->horizontalHeader()->width();
    pub::setColumnWidth(ui->tvList_fin, "0.1^0.15^0.1^0.05^0.1^0.1^0.1^0.1^0^0^0^0^0^0^0^0", width, 15);


}

void Form_kccx::on_pbDump_clicked()
{
    if(mdList1->rowCount() < 1)
    {
        QMessageBox::information(this, "注意", "无可导出数据 ！" );
        return ;
    }
    ui->pbDump->setEnabled(false);
    qApp->processEvents();
    pub::export_sql_to_excel(last_query_sql, qro);
    ui->pbDump->setEnabled(true);
}

void Form_kccx::on_pbInventory_clicked()
{
    //drug_dept_code,
    QString dept_code = "";
    if(ui->cbbStoreDept->currentText() != "全部")
    {
        dept_code = pub::getStr(store_depts, "^", pub::searchStr(store_depts, "^", ui->cbbStoreDept->currentText()) + 1);
    }
    else
    {
        QMessageBox::information(this, "注意", "生成盘点表需要指定具体库房！" );
        return ;
    }
    QString sq;

    // 按厂家划分

    sq = "SELECT 库房名称, 柜号, 药品名称, 规格, 生产厂家, 包装单位, 账面数量, 挂账, 账面数量 - 挂账 实际数量,";
    sq += "CASE WHEN (whole_package_num = 0 OR whole_package_num IS NULL) THEN 0"
          " ELSE FLOOR ((账面数量 - 挂账) / whole_package_num) END 件数,"
          " CASE WHEN (whole_package_num = 0 OR whole_package_num IS NULL) THEN 账面数量 - 挂账"
          " ELSE MOD (账面数量 - 挂账, whole_package_num )END 零数 "
          " from "
          " (SELECT 柜号, 库房名称, 药品名称, 规格, 生产厂家, 包装单位, 账面数量,"
          " ( CASE WHEN 挂账数量 IS NULL THEN 0 ELSE 挂账数量 END ) 挂账,"
          " (CASE WHEN whole_package_num IS NULL THEN 0 ELSE whole_package_num END ) whole_package_num"
          " FROM "
          " (SELECT ( SELECT NVL (position, '-') FROM drug_base_info WHERE dept_code = ST.drug_dept_code "
          "AND drug_code = 药品编码  AND ROWNUM = 1 ) 柜号,"
          " 库房名称, 药品名称, 规格,包装单位, 账面数量,"
          " (SELECT fac_name FROM pha_com_company WHERE fac_code = PRODUCER_CODE AND ROWNUM = 1 ) 生产厂家,"
          " (SELECT NVL (package_num, 0) FROM drug_base_info WHERE dept_code = ST.drug_dept_code "
          "AND drug_code = 药品编码 AND ROWNUM = 1 ) whole_package_num,"
          " (SELECT NVL (recal, 0) FROM drug_base_info "
          " WHERE drug_code = 药品编码 and dept_code = ST.drug_dept_code AND ROWNUM = 1 ) 挂账数量"
          " FROM"
          " (SELECT (SELECT dept_name FROM COM_DEPARTMENT WHERE DEPT_CODE = STO.drug_dept_code AND ROWNUM = 1 ) 库房名称,"
          " sto.trade_name 药品名称,sto.specs 规格, max(STO.PRODUCER_CODE) PRODUCER_CODE, sto.pack_unit 包装单位,"
          " SUM (store_sum / pack_qty) 账面数量, sto.drug_code 药品编码, STO.drug_dept_code"
          " FROM pha_com_storage sto WHERE sto.store_sum > 0 ";

    if(!dept_code.isEmpty())
    {
        sq += " and sto.drug_dept_code = '" +  dept_code + "'";
    }

    sq += " group by STO.drug_dept_code, sto.trade_name, sto.specs, sto.pack_unit,  sto.drug_code) st )) ";
    sq += " ORDER BY 柜号";

    qDebug()<<"提取盘点表："<<sq;
    mdList3->setQuery(sq, pub::db_o);

    if(mdList3->rowCount() < 1)
    {
        QMessageBox::information(this, "注意", "指定库房无数据！" );
        return;
    }

    ui->tvList_fin->setModel(mdList3);
    int width = ui->tvList_fin->horizontalHeader()->width();
    pub::setColumnWidth(ui->tvList_fin, "0.08^0.05^0.2^0.1^0.18^0.06^0.06^0.06^0.06^0.06^0.06", width, 11);


    QMessageBox::StandardButton bt;
    bt = QMessageBox::warning(NULL, "注意", "报表生成完毕，是否打印输出？",QMessageBox::Yes|QMessageBox::No);
    if(bt == QMessageBox::No){
        return;
    }

    QJsonDocument d;
    QJsonArray jsa_val;    //数值

    QJsonObject jot;  // 临时json
    int row = 3;

    int rowCount = 0;

    qro->exec(sq);
    while(qro->next())
    {
        row ++;
        rowCount ++ ;

        jot.insert("val", QString::number(rowCount));
        jot.insert("row", row);
        jot.insert("col", 1);
        jsa_val << jot;

        jot.insert("val", qro->value("药品名称").toString());
        jot.insert("row", row);
        jot.insert("col", 2);
        jsa_val << jot;

        jot.insert("val", qro->value("规格").toString());
        jot.insert("row", row);
        jot.insert("col", 3);
        jsa_val << jot;

        jot.insert("val", qro->value("包装单位").toString());
        jot.insert("row", row);
        jot.insert("col", 4);
        jsa_val << jot;

        jot.insert("val", qro->value("账面数量").toString());
        jot.insert("row", row);
        jot.insert("col", 5);
        jsa_val << jot;

        jot.insert("val", qro->value("挂账").toString());
        jot.insert("row", row);
        jot.insert("col", 6);
        jsa_val << jot;

        jot.insert("val", qro->value("实际数量").toString());
        jot.insert("row", row);
        jot.insert("col", 7);
        jsa_val << jot;

        jot.insert("val", qro->value("件数").toString());
        jot.insert("row", row);
        jot.insert("col", 8);
        jsa_val << jot;

        jot.insert("val", qro->value("零数").toString());
        jot.insert("row", row);
        jot.insert("col", 9);
        jsa_val << jot;

    }

    jot.insert("val", "库房：" + ui->cbbStoreDept->currentText());
    jot.insert("row", 2);
    jot.insert("col", 1);
    jsa_val << jot;

    jot.insert("val", "品种数量：" + QString::number(rowCount));
    jot.insert("row", 2);
    jot.insert("col", 3);
    jsa_val << jot;

    jot.insert("val", "打印时间：" + QDateTime::currentDateTime().toString("yyyy-MM-dd HH:mm:ss"));
    jot.insert("row", 2);
    jot.insert("col", 5);
    jsa_val << jot;


    QJsonArray jsa_rng;    //表格区域
    jot = d.object();
    QString rg = QString("A4:I%1").arg(QString::number( rowCount + 3 ));
    jot.insert("range", rg);
    jsa_rng << jot;

    QJsonArray jsa_clr;    //清除区域
    jot = d.object();
    jot.insert("row1",4);
    jot.insert("row2",rowCount);
    jot.insert("col1",1);
    jot.insert("col2",9);
    jsa_clr << jot;


    QJsonObject jo; //打印参数
    jo.insert("excel_model", "/sheets/pandianbiao.xls");
    jo.insert("cell_value", jsa_val);
    jo.insert("grid_range", jsa_rng);

    pub::printOutExcel(jo);

}
